import openpyxl
import pymysql
import yaml


def read_excel(fun_type, sheet_name):
    with open('parameters.yml', 'r') as file:
        yaml_data = yaml.safe_load(file)
    wb = openpyxl.load_workbook(yaml_data['excel']['filename'])
    sheet = wb[sheet_name]
    data = []
    for i in sheet.values:
        if i[0] == fun_type:
            data.append(i[1:])
    wb.close()
    return data


def db_select(sql):
    with open('parameters.yml', 'r') as file:
        yaml_data = yaml.safe_load(file)
    db = pymysql.connect(host=yaml_data['Mysql']['host'],
                         user=yaml_data['Mysql']['user'],
                         password=yaml_data['Mysql']['password'],
                         database=yaml_data['Mysql']['database'],
                         port=yaml_data['Mysql']['port'],
                         charset=yaml_data['Mysql']['charset'])
    cursor = db.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    db.commit()
    cursor.close()
    db.close()
    return result


def db_update(sql):
    with open('parameters.yml', 'r') as file:
        yaml_data = yaml.safe_load(file)
    db = pymysql.connect(host=yaml_data['Mysql']['host'],
                         user=yaml_data['Mysql']['user'],
                         password=yaml_data['Mysql']['password'],
                         database=yaml_data['Mysql']['database'],
                         port=yaml_data['Mysql']['port'],
                         charset=yaml_data['Mysql']['charset'])
    cursor = db.cursor()
    cursor.execute(sql)
    db.commit()
    cursor.close()
    db.close()
